IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncDatabase' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncDatabase
GO

CREATE PROCEDURE dbo.SaveSrvrInstncDatabase
     @SrvrInstncId             int,
     @PackageExecutionId       bigint,
     @DatabaseName             varchar(128),
     @DatabaseId               int           = NULL,
     @DatabaseOwnerName        varchar(128)  = NULL,
     @DateCreated              datetime      = NULL,
     @DatabaseReadOnlyFlag     tinyint       = NULL,
     @CompatibilityLevel       varchar(10)   = NULL,
     @Collation                varchar(128)  = NULL,
     @DBStatus                 varchar(30)   = NULL,
     @RecoveryModel            varchar(15)   = NULL,
     @AccessLevel              varchar(30)   = NULL,
     @AutoCloseFlag            tinyint       = NULL,
     @AutoCreateStatsFlag      tinyint       = NULL,
     @AutoShrinkFlag           tinyint       = NULL,
     @AutoUpdateStatsFlag      tinyint       = NULL,
     @AutoUpdateStatsAsyncFlag tinyint       = NULL,
     @RecursiveTriggersFlag    tinyint       = NULL,
     @TrustworthyFlag          tinyint       = NULL,
     @BrokerEnabledFlag        tinyint       = NULL,
     @EncryptionFlag           tinyint       = NULL,
     @DatabaseDeletedFlag      tinyint       = 0
AS
/*
 *
 * SaveSrvrInstncDatabase
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/20/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @DBIdForSrv       int,
             @StartDt          datetime,
             @SrvrInstncTypeId int,
             @MaxPEI           bigint,
             @RC               int,
             @NOW              datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Get The server Type
     SELECT @SrvrInstncTypeId = SrvrInstncTypeId
     FROM   dbo.SrvrInstnc 
     WHERE  SrvrInstncId = @SrvrInstncId

     -- Handle special case that the server is local, we can correct any values
     -- that are NULL
     IF EXISTS ( SELECT 'X' 
                 FROM   dbo.SrvrInstnc 
                 WHERE  SrvrInstncId       = @SrvrInstncId 
                 AND    SrvrInstncFullName = @@SERVERNAME )
     BEGIN
          SELECT @DatabaseId               = COALESCE( @DatabaseId, CONVERT( int, d.dbid ) ), 
                 @DatabaseOwnerName        = COALESCE( @DatabaseOwnerName, CONVERT( varchar(128), l.name ) ),
                 @DateCreated              = COALESCE( @DateCreated, CONVERT( datetime, d.crdate ) ), 
                 @DatabaseReadOnlyFlag     = COALESCE( @DatabaseReadOnlyFlag, CONVERT( tinyint, CASE DATABASEPROPERTYEX(d.name,'Updateability') WHEN N'READ_ONLY' THEN 1 ELSE 0 END ) ), 
                 @CompatibilityLevel       = COALESCE( @CompatibilityLevel, CONVERT( varchar(10), d.cmptlevel ) ), 
                 @Collation                = COALESCE( @Collation, CONVERT( varchar(128), DATABASEPROPERTYEX(d.name,'Collation') ) ), 
                 @DBStatus                 = COALESCE( @DBStatus, CONVERT( varchar(30), DATABASEPROPERTYEX(d.name,'Status') ) ), 
                 @RecoveryModel            = COALESCE( @RecoveryModel, CONVERT( varchar(15), DATABASEPROPERTYEX(d.name,'Recovery') ) ), 
                 @AccessLevel              = COALESCE( @AccessLevel, CONVERT( varchar(30), DATABASEPROPERTYEX(d.name,'UserAccess') ) ), 
                 @AutoCloseFlag            = COALESCE( @AutoCloseFlag, CONVERT( tinyint, DATABASEPROPERTYEX(d.name,'IsAutoClose') ) ), 
                 @AutoCreateStatsFlag      = COALESCE( @AutoCreateStatsFlag, CONVERT( tinyint, DATABASEPROPERTYEX(d.name,'IsAutoCreateStatistics') ) ), 
                 @AutoShrinkFlag           = COALESCE( @AutoShrinkFlag, CONVERT( tinyint, DATABASEPROPERTYEX(d.name,'IsAutoShrink ') ) ), 
                 @AutoUpdateStatsFlag      = COALESCE( @AutoUpdateStatsFlag, CONVERT( tinyint, DATABASEPROPERTYEX(d.name,'IsAutoUpdateStatistics') ) ), 
                 @AutoUpdateStatsAsyncFlag = COALESCE( @AutoUpdateStatsAsyncFlag, CONVERT( tinyint, NULL ) ), 
                 @RecursiveTriggersFlag    = COALESCE( @RecursiveTriggersFlag, CONVERT( tinyint, NULL ) ), 
                 @TrustworthyFlag          = COALESCE( @TrustworthyFlag, CONVERT( tinyint, NULL ) ), 
                 @BrokerEnabledFlag        = COALESCE( @BrokerEnabledFlag, CONVERT( tinyint, NULL ) ), 
                 @EncryptionFlag           = COALESCE( @EncryptionFlag, CONVERT( tinyint, NULL ) )
          FROM   master.dbo.sysdatabases d LEFT OUTER JOIN master.dbo.syslogins l 
                    ON d.sid = l.sid
          WHERE  d.name = @DatabaseName 
     END

     -- Condition All Flags
     SELECT @DatabaseReadOnlyFlag     = CASE WHEN @DatabaseReadOnlyFlag     != 1 OR @DatabaseReadOnlyFlag     IS NULL THEN 0 ELSE 1 END,
            @AutoCloseFlag            = CASE WHEN @AutoCloseFlag            != 1 OR @AutoCloseFlag            IS NULL THEN 0 ELSE 1 END,
            @AutoCreateStatsFlag      = CASE WHEN @AutoCreateStatsFlag      != 1 OR @AutoCreateStatsFlag      IS NULL THEN 0 ELSE 1 END,
            @AutoShrinkFlag           = CASE WHEN @AutoShrinkFlag           != 1 OR @AutoShrinkFlag           IS NULL THEN 0 ELSE 1 END,
            @AutoUpdateStatsFlag      = CASE WHEN @AutoUpdateStatsFlag      != 1 OR @AutoUpdateStatsFlag      IS NULL THEN 0 ELSE 1 END,
            @AutoUpdateStatsAsyncFlag = CASE WHEN @AutoUpdateStatsAsyncFlag != 1 OR @AutoUpdateStatsAsyncFlag IS NULL THEN 0 ELSE 1 END,
            @RecursiveTriggersFlag    = CASE WHEN @RecursiveTriggersFlag    != 1 OR @RecursiveTriggersFlag    IS NULL THEN 0 ELSE 1 END,
            @TrustworthyFlag          = CASE WHEN @TrustworthyFlag          != 1 OR @TrustworthyFlag          IS NULL THEN 0 ELSE 1 END,
            @BrokerEnabledFlag        = CASE WHEN @BrokerEnabledFlag        != 1 OR @BrokerEnabledFlag        IS NULL THEN 0 ELSE 1 END,
            @EncryptionFlag           = CASE WHEN @EncryptionFlag           != 1 OR @EncryptionFlag           IS NULL THEN 0 ELSE 1 END,
            @DatabaseDeletedFlag      = CASE WHEN @DatabaseDeletedFlag      != 1 OR @DatabaseDeletedFlag      IS NULL THEN 0 ELSE 1 END

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncDatabase
                     WHERE  SrvrInstncId = @SrvrInstncId 
                     AND    DatabaseName = @DatabaseName )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          INSERT dbo.SrvrInstncDatabase 
               ( SrvrInstncId,
                 PackageExecutionId,
                 DatabaseName,
                 EnabledFlag,
                 LastModDt )
          SELECT @SrvrInstncId,
                 @PackageExecutionId,
                 @DatabaseName,
                 1, -- EnabledFlag
                 CURRENT_TIMESTAMP

          SELECT @DBIdForSrv = SCOPE_IDENTITY()          

          IF ( @SrvrInstncTypeId = 1 ) -- MSSQL
          BEGIN
               INSERT dbo.SrvrInstncDatabaseMSSQL
                    ( SrvrInstncDatabaseId,
                      PackageExecutionId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      DatabaseId,
                      DatabaseOwnerName,
                      DateCreated,
                      DatabaseReadOnlyFlag,
                      CompatibilityLevel,
                      Collation,
                      DBStatus,
                      RecoveryModel,
                      AccessLevel,
                      AutoCloseFlag,
                      AutoCreateStatsFlag,
                      AutoShrinkFlag,
                      AutoUpdateStatsFlag,
                      AutoUpdateStatsAsyncFlag,
                      RecursiveTriggersFlag,
                      TrustworthyFlag,
                      BrokerEnabledFlag,
                      EncryptionFlag )
               SELECT @DBIdForSrv,
                      @PackageExecutionId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @DatabaseId,
                      @DatabaseOwnerName,
                      @DateCreated,
                      @DatabaseReadOnlyFlag,
                      @CompatibilityLevel,
                      @Collation,
                      @DBStatus,
                      @RecoveryModel,
                      @AccessLevel,
                      @AutoCloseFlag,
                      @AutoCreateStatsFlag,
                      @AutoShrinkFlag,
                      @AutoUpdateStatsFlag,
                      @AutoUpdateStatsAsyncFlag,
                      @RecursiveTriggersFlag,
                      @TrustworthyFlag,
                      @BrokerEnabledFlag,
                      @EncryptionFlag
          END -- MSSQL New Insert

          IF ( @SrvrInstncTypeId = 2 ) -- Oracle
          BEGIN
               RAISERROR( 'Oracle Is not currently implemented', 16, 1 )
          END

          IF ( @SrvrInstncTypeId = 3 ) -- Sybase
          BEGIN
               RAISERROR( 'Sybase Is not currently implemented', 16, 1 )
          END
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the Database Id
          SELECT @DBIdForSrv = SrvrInstncDatabaseId
          FROM   dbo.SrvrInstncDatabase
          WHERE  SrvrInstncId = @SrvrInstncId
          AND    DatabaseName = @DatabaseName

          -- Type Specific Work
          IF ( @SrvrInstncTypeId = 1 ) -- MSSQL
          BEGIN
               -- Get the startdt for the current record, if we are here one should
               -- be available, we perform a little trick here as well, taking 
               -- advantage or the ORDER BY  and making sure we have the maximum
               -- StartDt
               SELECT @StartDt = StartDt
               FROM   dbo.SrvrInstncDatabaseMSSQL
               WHERE  SrvrInstncDatabaseId = @DBIdForSrv
               AND    CurrentRecordFlag    = 1
               ORDER BY StartDt

               -- Reset current record flag to zero and set the end date to now minus
               -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
               -- not handle a difference less than 3ms.  Therefore it ends right before
               -- the new record starts with no overlap.
               UPDATE dbo.SrvrInstncDatabaseMSSQL 
               SET    EndDt             = DATEADD( ms, -3, @NOW ),
                      CurrentRecordFlag = 0
               WHERE  SrvrInstncDatabaseId = @DBIdForSrv
               AND    CurrentRecordFlag    = 1

               IF ( @DatabaseDeletedFlag = 0 )
               BEGIN -- Do regular checked insert
                    UPDATE dbo.SrvrInstncDatabase
                    SET    EnabledFlag = 1,
                           LastModDt   = CURRENT_TIMESTAMP
                    WHERE  SrvrInstncDatabaseId = @DBIdForSrv
                    AND    EnabledFlag = 0

                    INSERT dbo.SrvrInstncDatabaseMSSQL 
                         ( SrvrInstncDatabaseId,
                           PackageExecutionId,
                           StartDt,
                           EndDt,
                           CurrentRecordFlag,
                           DatabaseDeletedFlag,
                           DatabaseId,
                           DatabaseOwnerName,
                           DateCreated,
                           DatabaseReadOnlyFlag,
                           CompatibilityLevel,
                           Collation,
                           DBStatus,
                           RecoveryModel,
                           AccessLevel,
                           AutoCloseFlag,
                           AutoCreateStatsFlag,
                           AutoShrinkFlag,
                           AutoUpdateStatsFlag,
                           AutoUpdateStatsAsyncFlag,
                           RecursiveTriggersFlag,
                           TrustworthyFlag,
                           BrokerEnabledFlag,
                           EncryptionFlag )
                    SELECT @DBIdForSrv,
                           @PackageExecutionId,
                           @NOW, -- StartDt
                           '12/31/9999', -- EndDt
                           1, -- CurrentRecordFlag
                           DatabaseDeletedFlag = 0,
                           CASE WHEN DatabaseId         IS NOT NULL AND @DatabaseId         IS NULL THEN DatabaseId         ELSE @DatabaseId         END,
                           CASE WHEN DatabaseOwnerName  IS NOT NULL AND @DatabaseOwnerName  IS NULL THEN DatabaseOwnerName  ELSE @DatabaseOwnerName  END,
                           CASE WHEN DateCreated        IS NOT NULL AND @DateCreated        IS NULL THEN DateCreated        ELSE @DateCreated        END,
                           @DatabaseReadOnlyFlag,
                           CASE WHEN CompatibilityLevel IS NOT NULL AND @CompatibilityLevel IS NULL THEN CompatibilityLevel ELSE @CompatibilityLevel END,
                           CASE WHEN Collation          IS NOT NULL AND @Collation          IS NULL THEN Collation          ELSE @Collation          END,
                           CASE WHEN DBStatus           IS NOT NULL AND @DBStatus           IS NULL THEN DBStatus           ELSE @DBStatus           END,
                           CASE WHEN RecoveryModel      IS NOT NULL AND @RecoveryModel      IS NULL THEN RecoveryModel      ELSE @RecoveryModel      END,
                           CASE WHEN AccessLevel        IS NOT NULL AND @AccessLevel        IS NULL THEN AccessLevel        ELSE @AccessLevel        END,
                           @AutoCloseFlag,
                           @AutoCreateStatsFlag,
                           @AutoShrinkFlag,
                           @AutoUpdateStatsFlag,
                           @AutoUpdateStatsAsyncFlag,
                           @RecursiveTriggersFlag,
                           @TrustworthyFlag,
                           @BrokerEnabledFlag,
                           @EncryptionFlag
                    FROM   dbo.SrvrInstncDatabaseMSSQL
                    WHERE  SrvrInstncDatabaseId = @DBIdForSrv
                    AND    StartDt              = @StartDt
                    -- Decide if there will be an insert
                    AND  ( DatabaseDeletedFlag = 1
                           -- 1 = 0 -- Placeholder, always false, don't want to insert on accident
                        OR ( @DatabaseId IS NOT NULL               AND DatabaseId IS NULL )
                        OR ( @DatabaseOwnerName IS NOT NULL        AND DatabaseOwnerName IS NULL )
                        --OR ( @DateCreated IS NOT NULL              AND DateCreated IS NULL ) -- We aren't evaluating DateCreated
                        OR ( @DatabaseReadOnlyFlag IS NOT NULL     AND DatabaseReadOnlyFlag IS NULL )
                        OR ( @CompatibilityLevel IS NOT NULL       AND CompatibilityLevel IS NULL )
                        OR ( @Collation IS NOT NULL                AND Collation IS NULL )
                        OR ( @DBStatus IS NOT NULL                 AND DBStatus IS NULL )
                        OR ( @RecoveryModel IS NOT NULL            AND RecoveryModel IS NULL )
                        OR ( @AccessLevel IS NOT NULL              AND AccessLevel IS NULL )
                        OR ( @AutoCloseFlag IS NOT NULL            AND AutoCloseFlag IS NULL )
                        OR ( @AutoCreateStatsFlag IS NOT NULL      AND AutoCreateStatsFlag IS NULL )
                        OR ( @AutoShrinkFlag IS NOT NULL           AND AutoShrinkFlag IS NULL )
                        OR ( @AutoUpdateStatsFlag IS NOT NULL      AND AutoUpdateStatsFlag IS NULL )
                        OR ( @AutoUpdateStatsAsyncFlag IS NOT NULL AND AutoUpdateStatsAsyncFlag IS NULL )
                        OR ( @RecursiveTriggersFlag IS NOT NULL    AND RecursiveTriggersFlag IS NULL )
                        OR ( @TrustworthyFlag IS NOT NULL          AND TrustworthyFlag IS NULL )
                        OR ( @BrokerEnabledFlag IS NOT NULL        AND BrokerEnabledFlag IS NULL )
                        OR ( @EncryptionFlag IS NOT NULL           AND EncryptionFlag IS NULL )
                        OR ( @DatabaseId               != DatabaseId )
                        OR ( @DatabaseOwnerName        != DatabaseOwnerName )
                        --OR ( @DateCreated              != DateCreated ) -- We aren't evaluating DateCreated
                        OR ( @DatabaseReadOnlyFlag     != DatabaseReadOnlyFlag )
                        OR ( @CompatibilityLevel       != CompatibilityLevel )
                        OR ( @Collation                != Collation )
                        OR ( @DBStatus                 != DBStatus )
                        OR ( @RecoveryModel            != RecoveryModel )
                        OR ( @AccessLevel              != AccessLevel )
                        OR ( @AutoCloseFlag            != AutoCloseFlag )
                        OR ( @AutoCreateStatsFlag      != AutoCreateStatsFlag )
                        OR ( @AutoShrinkFlag           != AutoShrinkFlag )
                        OR ( @AutoUpdateStatsFlag      != AutoUpdateStatsFlag )
                        OR ( @AutoUpdateStatsAsyncFlag != AutoUpdateStatsAsyncFlag )
                        OR ( @RecursiveTriggersFlag    != RecursiveTriggersFlag )
                        OR ( @TrustworthyFlag          != TrustworthyFlag )
                        OR ( @BrokerEnabledFlag        != BrokerEnabledFlag )
                        OR ( @EncryptionFlag           != EncryptionFlag )
                        -- -- Taking this section out for now, basically if the 
                        -- -- record has data but the sproc call contains mostly 
                        -- -- nulls we don't want to wipe out data that we already 
                        -- -- have, so we won't accept this condition.
                        --OR ( @DatabaseId IS NULL                   AND DatabaseId IS NOT NULL )
                        --OR ( @DatabaseOwnerName IS NULL            AND DatabaseOwnerName IS NOT NULL )
                        ----OR ( @DateCreated IS NULL                  AND DateCreated IS NOT NULL ) -- We aren't evaluating DateCreated
                        --OR ( @DatabaseReadOnlyFlag IS NULL         AND DatabaseReadOnlyFlag IS NOT NULL )
                        --OR ( @CompatibilityLevel IS NULL           AND CompatibilityLevel IS NOT NULL )
                        --OR ( @Collation IS NULL                    AND Collation IS NOT NULL )
                        --OR ( @DBStatus IS NULL                     AND DBStatus IS NOT NULL )
                        --OR ( @RecoveryModel IS NULL                AND RecoveryModel IS NOT NULL )
                        --OR ( @AccessLevel IS NULL                  AND AccessLevel IS NOT NULL )
                        --OR ( @AutoCloseFlag IS NULL                AND AutoCloseFlag IS NOT NULL )
                        --OR ( @AutoCreateStatsFlag IS NULL          AND AutoCreateStatsFlag IS NOT NULL )
                        --OR ( @AutoShrinkFlag IS NULL               AND AutoShrinkFlag IS NOT NULL )
                        --OR ( @AutoUpdateStatsFlag IS NULL          AND AutoUpdateStatsFlag IS NOT NULL )
                        --OR ( @AutoUpdateStatsAsyncFlag IS NULL     AND AutoUpdateStatsAsyncFlag IS NOT NULL )
                        --OR ( @RecursiveTriggersFlag IS NULL        AND RecursiveTriggersFlag IS NOT NULL )
                        --OR ( @TrustworthyFlag IS NULL              AND TrustworthyFlag IS NOT NULL )
                        --OR ( @BrokerEnabledFlag IS NULL            AND BrokerEnabledFlag IS NOT NULL )
                        --OR ( @EncryptionFlag IS NULL               AND EncryptionFlag IS NOT NULL )
                         ) -- End of And-Or section

                    SELECT @RC = @@ROWCOUNT 
               END -- End of regular checked insert
               ELSE
               BEGIN -- Do database deleted insert
                    UPDATE dbo.SrvrInstncDatabase
                    SET    EnabledFlag = 0,
                           LastModDt   = CURRENT_TIMESTAMP
                    WHERE  SrvrInstncDatabaseId = @DBIdForSrv

                    INSERT dbo.SrvrInstncDatabaseMSSQL
                         ( SrvrInstncDatabaseId,
                           PackageExecutionId,
                           StartDt,
                           EndDt,
                           CurrentRecordFlag,
                           DatabaseDeletedFlag,
                           DatabaseId,
                           DatabaseOwnerName,
                           DateCreated,
                           DatabaseReadOnlyFlag,
                           CompatibilityLevel,
                           Collation,
                           DBStatus,
                           RecoveryModel,
                           AccessLevel,
                           AutoCloseFlag,
                           AutoCreateStatsFlag,
                           AutoShrinkFlag,
                           AutoUpdateStatsFlag,
                           AutoUpdateStatsAsyncFlag,
                           RecursiveTriggersFlag,
                           TrustworthyFlag,
                           BrokerEnabledFlag,
                           EncryptionFlag )
                    SELECT SrvrInstncDatabaseId,
                           @PackageExecutionId,
                           @NOW, -- StartDt
                           '12/31/9999', -- EndDt
                           1, -- CurrentRecordFlag
                           DatabaseDeletedFlag = 1,
                           DatabaseId,
                           DatabaseOwnerName,
                           DateCreated,
                           DatabaseReadOnlyFlag,
                           CompatibilityLevel,
                           Collation,
                           DBStatus,
                           RecoveryModel,
                           AccessLevel,
                           AutoCloseFlag,
                           AutoCreateStatsFlag,
                           AutoShrinkFlag,
                           AutoUpdateStatsFlag,
                           AutoUpdateStatsAsyncFlag,
                           RecursiveTriggersFlag,
                           TrustworthyFlag,
                           BrokerEnabledFlag,
                           EncryptionFlag
                    FROM   dbo.SrvrInstncDatabaseMSSQL
                    WHERE  SrvrInstncDatabaseId = @DBIdForSrv
                    AND    StartDt              = @StartDt

                    SELECT @RC = @@ROWCOUNT 
               END
          END -- End of Type 1 Existing Insert

          IF ( @SrvrInstncTypeId = 2 ) -- Oracle
          BEGIN
               RAISERROR( 'Oracle Is not currently implemented', 16, 1 )
          END

          IF ( @SrvrInstncTypeId = 3 ) -- Sybase
          BEGIN
               RAISERROR( 'Sybase Is not currently implemented', 16, 1 )
          END

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance database record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncDatabase;
GO
